//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Copyright 2007 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   CATALOG_DTL
// AUTHOR           :   Saint Peng 
// DATE CREATE      :   2007/07/17
// DESCRIPTION      :   a COMMON DAO file for the function, CATALOG_DTL
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2007/07/17   Saint Peng      1.0                 source is generated.
//
//
//
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

package com.erp.app.catalog.catalogDtl.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.catalog.catalogDtl.dao.CatalogDtlCommonDao;
import com.erp.app.catalog.catalogDtl.vo.CatalogDtlVo;
import com.erp.app.catalog.catalogDtl.bizLogic.CatalogDtlCommonUtil;

import com.erp.app.common.ProjectConstants;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigInteger;
import com.erp.framework.types.CustomSqlDate;
import com.erp.framework.util.CommonUtil;
import com.erp.app.catalog.catalogDtl.criteria.CatalogDtlSrchCriteria;

/**
* <p>A <strong>CommonDao</strong> provides database access functions
*  for delegate classes.
*
* @author          Saint Peng 
* @version 1.0     2007/07/17
* @since           FrameWork 1.1
*
*/
public class CatalogDtlCommonDao {

	private CatalogDtlCommonUtil util = new CatalogDtlCommonUtil();

	/**
	* Returns the SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Saint Peng 
	* @since      Framework 1.1
	*/
	public static String getHdrSqlQuery(SimpleBaseSrchCriteria obj) {

		CatalogDtlSrchCriteria criteria = (CatalogDtlSrchCriteria) obj;
		String sql = "";
		String whereClause = hdrToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "ORDER BY COMPANY_CODE, DTL_ID";

		if(!criteria.isSearchResult()){
			sql = "SELECT COMPANY_CODE, DTL_ID, LANG_CODE, DTL_TYPE, DTL_NAME, "
			+ "UOM, RECURSIVE_TIME, STATUS, PRECISION, LENGTH, "
			+ "OPTION_CODE, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, "
			+ "DC_IND, DC_DATE "
			+ "FROM T_CATALOG_DTL "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		} else {
			sql = "SELECT COMPANY_CODE, DTL_ID, LANG_CODE, DTL_TYPE, DTL_NAME, "
				+ "UOM, RECURSIVE_TIME, STATUS, PRECISION, LENGTH, "
				+ "OPTION_CODE, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, "
				+ "DC_IND, DC_DATE "
				+ "FROM T_CATALOG_DTL "
				+ whereClause
				+ groupByClause
				+ orderByClause;
		}

		return sql;
	}

	/**
	* Sets the values of a result set to a vo for a search screen
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Saint Peng 
	* @since      Framework 1.1
	*/
	public static SimpleBaseVo getHdrSearchResultSetValues(ResultSet rs) throws SQLException {

		CatalogDtlVo vo = new CatalogDtlVo();

		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
		vo.setDtlId(CommonUtil.bigDecimalToStr(rs.getBigDecimal("DTL_ID")));
		vo.setHdrLangCode(rs.getString("LANG_CODE"));
		vo.setHdrDtlType(rs.getString("DTL_TYPE"));
		vo.setHdrDtlName(rs.getString("DTL_NAME"));
		vo.setHdrUom(rs.getString("UOM"));
		vo.setHdrRecursiveTime(CustomBigInteger.customize(rs.getBigDecimal("RECURSIVE_TIME")));
		vo.setHdrStatus(rs.getString("STATUS"));
		vo.setHdrPrecision(CustomBigInteger.customize(rs.getBigDecimal("PRECISION")));
		vo.setHdrLength(CustomBigInteger.customize(rs.getBigDecimal("LENGTH")));
		vo.setHdrOptionCode(rs.getString("OPTION_CODE"));
		vo.setCreateUser(rs.getString("CREATE_USER"));
		vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
		vo.setUpdateUser(rs.getString("UPDATE_USER"));
		vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
		vo.setHdrDcInd(rs.getString("DC_IND"));
		vo.setHdrDcDate(CustomSqlDate.customize(rs.getDate("DC_DATE")));

		return vo;
	}

	/**
	* Returns the where clause of a SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing the where clause
	*
	* @author     Saint Peng 
	* @since      Framework 1.1
	*/
	public static String hdrToWhereClause(CatalogDtlSrchCriteria crit) {

		CatalogDtlSrchCriteria criteria = (CatalogDtlSrchCriteria) crit;
		StringBuffer whereClause = new StringBuffer();

		if (criteria.getSrchCompanyCode() != null) {
			whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
			whereClause.append(" COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
		}
		
		if (criteria.getSrchHdrDtlTypeFr() != null) {
			whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
			whereClause.append(" DTL_TYPE = '" + criteria.getSrchHdrDtlTypeFr().trim().toUpperCase()+"' ");
		}

		
		if (criteria.getSrchHdrDtlNameFr() != null) {
			whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
			whereClause.append(" upper(DTL_NAME) like '%" + criteria.getSrchHdrDtlNameFr().trim().toUpperCase()+"%' ");
		}

		
		if (criteria.getSrchHdrStatus() != null) {
			whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
			whereClause.append(" STATUS = '" + criteria.getSrchHdrStatus().trim().toUpperCase()+"' ");
		}

		return whereClause.toString();
	}

}
